-- =============================================
-- Author:		<Author,Grupo 1>
-- Create date: <Create Date,,>
-- Description:	<Description,Funcion que busca las materias posibles para la prematricua>
-- =============================================
CREATE PROCEDURE [dbo].[ConsultarMateriasPrematricula] 
	@id_estudiante int,
	@semestreActual int

AS
BEGIN

	SET NOCOUNT ON;
		select MateriasPensumConPrelacion.id_Materia, MateriasPensumConPrelacion.nombreMateria, MateriasPensumConPrelacion.semestre, MateriasPensumConPrelacion.unidadesDeCredito
		FROM	(select id_Materia,nombreMateria, fk_materia_prelacion, semestre, unidadesDeCredito
					from PensumMateria, pensum,Materia, Prelacion p
					where id_Materia = fk_id_materia and id_Pensum = fk_id_pensum and id_Materia = fk_materia_actual and id_Pensum = 1) MateriasPensumConPrelacion,
					(select id_Materia
					from materia, estudiante, HistorialEstudianteMateria hm
					where id_Materia = fk_materia and id_estudiante = fk_estudiante and id_estudiante = @id_estudiante and hm.notaDefinitiva > 09) MateriasAprobadas
		where MateriasPensumConPrelacion.fk_materia_prelacion = MateriasAprobadas.id_Materia
		GROUP BY MateriasPensumConPrelacion.id_Materia, MateriasPensumConPrelacion.nombreMateria, MateriasPensumConPrelacion.semestre, MateriasPensumConPrelacion.unidadesDeCredito

		union ALL

		select b.id_Materia,b.nombreMateria,b.semestre,b.unidadesDeCredito
		from (select a.id_Materia, a.nombreMateria, a.semestre,a.unidadesDeCredito
										from (select id_Materia, nombreMateria,semestre,unidadesDeCredito from materia, PensumMateria, Pensum where id_Materia = fk_id_materia and id_Pensum = fk_id_pensum and id_Pensum = 1) a
										left outer join Prelacion
										on fk_materia_actual = a.id_Materia
										where fk_materia_prelacion is NULL and a.semestre >= @semestreActual and a.semestre <= (@semestreActual + 2)) b

		left OUTER JOIN (select id_Materia
									from materia, estudiante, HistorialEstudianteMateria hm
									where id_Materia = fk_materia and id_estudiante = fk_estudiante and id_estudiante = @id_estudiante and hm.notaDefinitiva > 09) a

		on b.id_Materia = a.id_Materia
		where a.id_Materia is NULL

		ORDER BY id_materia
    
END